<?php
/**
 * @copyright Copyright (c) 2021 勾股工作室
 * @license https://opensource.org/licenses/GPL-3.0
 * @link https://www.gougucms.com
 */
declare (strict_types = 1);
namespace app\api\controller;

use app\api\BaseController;
use think\facade\Db;
use think\facade\Env;
//use think\faced\Filesystem;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date as Shared;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Overtrue\Pinyin\Pinyin;


class Import extends BaseController
{

	//上传文件
	public function uploader(){
		 $file[]= request()->file('file');
		 // 日期前綴
		// $dataPath = date('Ym');
		 //$md5 = $file[0]->hash('md5');
		 $savename = \think\facade\Filesystem::disk('public')->putFile($dataPath, $file[0], 'md5');
		  $file = request()->file('file');  //这里‘file’是你提交时的name
    try{
        // validate(['goodFile' => [     //goodFile是你自定义的键名，目的是为了对check里数组中的
        //     'fileSize' =>50*1024*1024,//goodFile字段值进行验证;允许文件大小
        //     'fileExt'  => array('mp3','wav'),  //允许的文件后缀
        //     ]])->check(['goodFile'=>$file]);//也就是对上传的$file进行验证
        $saveName = \think\facade\Filesystem::disk('public')->putFile( 'xlsx', $file);//保存文件名
        $arr = ['status' => 200, 'msg' => '成功', 'path' => app()->getRootPath().'public/storage'.$saveName];
        return json($arr); //返回标准json格式
    }catch (\Exception $e) {
        return $this->exceptionHandle($e,'上传失败!' . $e->getMessage(),'json','');
    }
	}

/**
 * 批量上传班级信息
 * */
 public function import_class(){
 	$files[]= request()->file('file');
    // dump($files);die;
 	 try{
 		$dataPath = 'xlsx/'.date('Ym');
		// 验证文件大小，名称等是否正确
		validate(['xlsfile'=>'filesiz:10*1024*1024|fileExt:xls,xlsx'])
            ->check($files);
        $savename = \think\facade\Filesystem::disk('public')->putFile($dataPath, $files[0]);
        $fileExtendName = substr(strrchr($savename, '.'), 1);
        // dump($savename);die;
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
			$path = get_config('filesystem.disks.public.url');
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load('.'.$path . '/' .$savename);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
				return to_assign(1, '数据不能为空');
                exit();
            }

            $client_ip = request()->ip();//获取客户端ip
            for ($j = 2; $j <= $highestRow; $j++) {
            	$bj_name=$objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();
            	$grade_array=[' ','一','二','三','四','五','六'];
            	$grade_char = mb_substr($bj_name, 0, 1);
            	$grade=array_search($grade_char,$grade_array);//从班级中获取数据转化为年级
            	$bj_name_s=$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue();            	 
            	$bj_code=$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue();
            	$headteacher =$objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue();//课程类别
            	$woman=$objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue();//限报人数
            	$man=$objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue();//年级
            	$num=$objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue();
            	$sort=$objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue();
                $teacher= $objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue();         	

                $data[$j - 2] = [		
                    'bj_name' => $bj_name,
                    'headteacher'=>$headteacher,
                    'bj_name_s' => $bj_name_s,
                    'bj_code'=>$bj_code, 
                    'woman' => $woman,
                    'man' => $man,
                    'num' => $num,
                    'grade' => $grade,
                    'sort' => empty($sort)?0:$sort,//如果为空设置为0
                    'teacher'=>empty($teacher)?' ':$teacher,
                    'update_ip' => $client_ip,
                    'create_time' =>time(),
                    'update_time' =>time(),
                    'status'=> 1
                ];

            }
        

        // 批量添加数据
        $count = Db::name('UserClass')->insertAll($data);
        
        if ($count) {
            return to_assign(0, '导入成功, 共导入班级数据'.$count.'条');
        }
		else{
			return to_assign(1, '导入失败，请检查excel文件再试');
		}

 	}catch(ValidateException $e){ 
		return to_assign(1, $e->getMessage(),'json','');    
 	}
 }
/**
 * 批量上传课程信息
 * */
 public function import_course(){
 	$files[]= request()->file('file');
 	//return to_assign(0, '导入成功');
 	try{
 		$dataPath = date('Ym');
		// 验证文件大小，名称等是否正确
		validate(['xlsfile'=>'filesiz:10*1024*1024|fileExt:xls,xlsx'])
            ->check($files);
        $savename = \think\facade\Filesystem::disk('public')->putFile($dataPath, $files[0]);
        $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
			$path = get_config('filesystem.disks.public.url');
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load('.'.$path . '/' .$savename);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
				return to_assign(1, '数据不能为空');
                exit();
            }

            $client_ip = request()->ip();//获取客户端ip
            for ($j = 2; $j <= $highestRow; $j++) {
            	$cname=$objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();
            	$teacher=$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue();            	 
            	$mobile=$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue();
            	$category =$objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue();//课程类别
            	$number=$objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue();//限报人数
            	$grade=$objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue();//年级
            	$year=$objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue();
            	$room=$objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue();          	
            	$bz=$objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue();
            	$bz = !$bz ?'':$bz;//如果为null转为空字符串
                $data[$j - 2] = [		
                    'cname' => $cname,
                    'teacher'=>$teacher,
                    'mobile' => $mobile,
                    'category'=>$category, 
                    'number' => $number,
                    'grade' => $grade,
                    'year' => empty($year)?date('Y'):$year,//如果为空设置为当前年度
                    'room' => $room,
                    'bz' => $bz,
                    'shengyu'=>$number,
                    'update_ip' => $client_ip,
                    'create_time' =>time(),
                    'update_time' =>time(),
                    'status'=> 1
                ];

            }
        // return to_assign(0, '导入成功, 共导入课程数据'.json_encode($data)); 

        // 批量添加数据
        $count = Db::name('Course')->insertAll($data);
        
        if ($count) {
            return to_assign(0, '导入成功, 共导入课程数据'.$count.'条');
        }
		else{
			return to_assign(1, '导入失败，请检查excel文件再试');
		}

 	}catch(ValidateException $e){ 
		return to_assign(1, $e->getMessage());    
 	}
 }
/**
 * 批量上传学生信息
 * */
public function import_student(){
    $pinyin = new Pinyin();//实例化拼音
	
    //三年级1班 210101  曹鸿镝 男   13566651820 3.31023E+17 浙江省天台县赤城街道友谊公寓4幢303室    范妲丹 T11
    $sexs=['未知'=>0,'男'=>1,'女'=>2];
	$files[]= request()->file('file');

		$dataPath = date('Ym');
		// 验证文件大小，名称等是否正确
		validate(['xlsfile'=>'filesiz:10*1024*1024|fileExt:xls,xlsx'])
            ->check($files);
        $savename = \think\facade\Filesystem::disk('public')->putFile($dataPath, $files[0]);
        
        $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
			$path = get_config('filesystem.disks.public.url');
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load('.'.$path . '/' .$savename);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
				return to_assign(1, '数据不能为空');
                exit();
            }

            $insert_count=0;
            $error_count=0;
            for ($j = 2; $j <= $highestRow; $j++) {
            	
            	$bj_name=$objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();
                $bj_code=Db::name('UserClass')->where(['bj_name'=>$bj_name])->value('bj_code');

            	$st_code=$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue();

            	$name=$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue();
            	$sex =$objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue();
            	$mobile=$objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue();//
            	$sfz=$objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue();//
            	$address=$objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue();//
            	$bz=$objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue();//
                $pc=$objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue();
                //检测用户名是否已经存在，如果存在则在后面加上数字
                $username=$pinyin->permalink($name, '');
                $map=[['username','like','%'.$username.'%']];
                $count=Db::name('User')->where($map)->count();
                if($count>0){
                    $count++;
                    $username.=(string)$count;
                }
            	
            	if(!$bz){$bz='';}//
                $data[$j] = [		
                    'bj_code' => $bj_code,
                    //'grade'=>$grade,
                    'st_code' => $st_code,
                    'name'=>$name, 
                    'sex' => $sexs[$sex],
                    'mobile' => empty($mobile)?' ':$mobile,
                    'sfz' => empty($sfz)?" ":$sfz,
                    'address' => empty($address)?' ':$address,
                    'nickname'=> $name,
                    'username' =>$username,
                    'password'=>'123456',
                    'bz' => $bz,
                    'login_pc'=>$pc,
					'register_time' =>time(),
                    'last_login_time' =>time()
                ];
                $res=Db::name('User')->insert($data[$j]);
                if($res){
                    $insert_count++;
                }else{
                    $error_count++;
                }
            }
            // 批量添加数据
            //$count = Db::name('User')->insertAll($data);
         
            return to_assign(0, '导入成功, 共导入学生数据'.$insert_count.'条,导入失败'.$error_count.'条记录');
           


}


	
	//导入客户
	public function import_customer(){
        // 获取表单上传文件
        $file[]= request()->file('file');
		
		$param = get_params();
		$type = 'sea';
		if(isset($param['type'])){
			$type = $param['type'];
		}
        try {
            // 验证文件大小，名称等是否正确
            validate(['file' => 'filesize:51200|fileExt:xls,xlsx'])->check($file);
			// 日期前綴
			 $dataPath = date('Ym');
			 $md5 = $file[0]->hash('md5');
			 $savename = \think\facade\Filesystem::disk('public')->putFile($dataPath, $file[0], function () use ($md5) {
				 return $md5;
			 });
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
			$path = get_config('filesystem.disks.public.url');
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load('.'.$path . '/' .$savename);
            //$objPHPExcel = $objReader->load('./storage/202209/d11544d20b3ca1c1a5f8ce799c3b2433.xlsx');
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
				return to_assign(1, '数据不能为空');
                exit();
            }
			$name_array = []; 
			$source_array = Db::name('CustomerSource')->where(['status' => 1])->column('title', 'id');
			$grade_array = Db::name('CustomerGrade')->where(['status' => 1])->column('title', 'id');
			$industry_array = Db::name('Industry')->where(['status' => 1])->column('title', 'id');
					
            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            for ($j = 3; $j <= $highestRow; $j++) {
				$file_check = [];
				$name = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();
				if(empty($name)){
					continue;
				}
				$count_name = Db::name('Customer')->where('name',$name)->count();
				if($count_name>0){
					return to_assign(1, '第'.($j - 2).'行的客户名称已经存在');
				}
				if(in_array($name,$name_array)){
					return to_assign(1, '上传的文件存在相同的客户名称，请删除再操作');
				}
				array_push($name_array,$name);
				$source_id = arraySearch($source_array,$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue());
				$grade_id = arraySearch($grade_array,$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue());
				$industry_id = arraySearch($industry_array,$objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue());	
				
				$c_name = $objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue();
				$c_mobile = $objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue();
				$file_check['c_mobile'] = $c_mobile;
				$tax_num = $objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue();
				$bank = $objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue();
				$bank_sn = $objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue();
				$file_check['bank_sn'] = $bank_sn;
				$bank_no = $objPHPExcel->getActiveSheet()->getCell("K" . $j)->getValue();				
				$cperson_mobile = $objPHPExcel->getActiveSheet()->getCell("K" . $j)->getValue();				
				$address = $objPHPExcel->getActiveSheet()->getCell("L" . $j)->getValue();
				$content = $objPHPExcel->getActiveSheet()->getCell("M" . $j)->getValue();
				$market = $objPHPExcel->getActiveSheet()->getCell("N" . $j)->getValue();
				if(empty($c_name)){
					return to_assign(1, '第'.($j - 2).'行的客户联系人姓名没完善');
				}
				if(empty($c_mobile)){
					return to_assign(1, '第'.($j - 2).'行的客户联系人手机号码没完善');
				}
				$validate_mobile = \think\facade\Validate::rule([
					'c_mobile' => 'mobile',
				]);
				if (!$validate_mobile->check($file_check)) {
					return to_assign(1, '第'.($j - 2).'行的客户联系人手机号码格式错误');
				}
				if(empty($source_id)){
					return to_assign(1, '第'.($j - 2).'行的客户来源错误');
				}
				if(empty($grade_id)){
					return to_assign(1, '第'.($j - 2).'行的客户等级错误');
				}
				if(empty($industry_id)){
					return to_assign(1, '第'.($j - 2).'行的所属行业错误');
				}
				if(empty($tax_num)){
					$tax_num='';
				}
				if(empty($bank)){
					$bank='';
				}
				$validate_bank = \think\facade\Validate::rule([
					'bank_sn' => 'number',
				]);
				if(!empty($bank_sn)){
					if (!$validate_bank->check($file_check)) {
						return to_assign(1, '第'.($j - 2).'行的银行卡账号格式错误');
					}
				}
				else{
					$bank_sn='';
				}
				if(empty($bank_no)){
					$bank_no='';
				}
				if(empty($cperson_mobile)){
					$cperson_mobile='';
				}
				if(empty($address)){
					$address='';
				}
				if(empty($content)){
					$content='';
				}
				if(empty($market)){
					$market='';
				}
				$belong_uid = 0;
				$belong_did = 0;
				if($type != 'sea'){
					$belong_uid = $this->uid;
					$belong_did = $this->did;
				}
                $data[$j - 3] = [		
                    'name' => $name,
                    'source_id' => $source_id,
                    'grade_id' => $grade_id,
                    'industry_id' => $industry_id,
                    'tax_num' => $tax_num,
                    'bank' => $bank,
                    'bank_sn' => $bank_sn,
                    'bank_no' => $bank_no,
					'cperson_mobile' => $cperson_mobile,
					'address' => $address,
                    'content' => $content,
					'market' => $market,
                    'admin_id' => $this->uid,
                    'belong_uid' => $belong_uid,
                    'belong_did' => $belong_did,
                    'c_mobile' => $c_mobile,
                    'c_name' => $c_name,
                    'create_time' => time()
                ];
            }
            //dd($data);exit;
            // 批量添加数据
			$count=0;
			foreach ($data as $a => $aa) {	
				$cid = Customer::strict(false)->field(true)->insertGetId($aa);
				if($cid>0){
					$contact = [
						'name' => $aa['c_name'],
						'mobile' => $aa['c_mobile'],
						'sex' => 1,
						'cid' => $cid,
						'is_default' => 1,
						'create_time' => time(),
						'admin_id' => $this->uid
					];
					Db::name('CustomerContact')->strict(false)->field(true)->insert($contact);
					$count++;
				}
			}
            return to_assign(0, '共成功导入了'.$count.'条客户数据');
        } catch (\think\exception\ValidateException $e) {
			return to_assign(1, $e->getMessage());
        }
    }
}
